package com.huikeportal.controller;



import java.io.IOException;
import java.io.OutputStream;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.huikeportal.controller.common.ExcelCreate;
import com.huikeportal.controller.common.Lang;
import com.huikeportal.controller.common.Log;
import com.huikeportal.controller.common.MessageManager;
import com.huikeportal.model.DataTransInfo;
import com.huikeportal.model.Language;
import com.huikeportal.model.PosApplication;
import com.huikeportal.model.SiteAllInfo;
import com.huikeportal.model.TableColumnInfo;
import com.huikeportal.model.TransObject;
import com.huikeportal.model.UserInfo;
import com.huikeportal.model.common.CellVo;
import com.huikeportal.model.common.ExcelVo;
import com.huikeportal.model.common.PageDataVo;
import com.huikeportal.model.common.SheetVo;
import com.huikeportal.service.DataTransitionService;
import com.huikeportal.service.LoginService;

/**
 * 数据接口
 * 
 * @author focus
 * 
 */
@Controller
@Scope("prototype")
public class DataTransitionController {
	
	@Autowired
	private HttpSession session;
	
	@Autowired
	private DataTransitionService dataTransitionService;
	
	@Autowired
	private LoginService loginService;
	
	@RequestMapping("/dataTransition")
	public String dataTransitionManager(PageDataVo pageDataVo, ModelMap model) throws Exception {
		
		// 获取登录用户的信息
		UserInfo userInfo = (UserInfo) session.getAttribute("sessionUserName");
		userInfo = loginService.getUserInfo(userInfo);
		String menuHtml = loginService.getMenuHtml(userInfo, "dataTransition");
		
		model.addAttribute("menuHtml", menuHtml);// 左侧菜单html
		Language lang = Lang.getInfo("zh_cn");
		model.addAttribute("lang", lang);
		model.addAttribute("datetime",dataTransitionService.getDate());
		return "dataTransition";
	}
	
	@RequestMapping("/dataTransConfig")
	public String dataTransConfig(PageDataVo pageDataVo, ModelMap model) throws Exception {
		// 获取登录用户的信息
		UserInfo userInfo = (UserInfo) session.getAttribute("sessionUserName");
		userInfo = loginService.getUserInfo(userInfo);
		String menuHtml = loginService.getMenuHtml(userInfo, "dataTransition");
		
		// 获取用户权限下的所有广场
		List<SiteAllInfo> squareList = getAllGcList(userInfo);
		
		List<DataTransInfo> siteTypeList = new ArrayList<DataTransInfo>();
		DataTransInfo siteType = new DataTransInfo();
		siteType.setInnerid("300");
		siteType.setInnername("广场");
		siteTypeList.add(siteType);
		
		SiteAllInfo temp = squareList.get(0);
		List<DataTransInfo> siteNameList = new ArrayList<DataTransInfo>();
		if(temp!=null){
			DataTransInfo siteName = new DataTransInfo();
			siteName.setInnerid(temp.getSiteid());
			siteName.setInnername(temp.getSitename());
			siteNameList.add(siteName);
		}

		
		List<TransObject> transObjects = dataTransitionService.getTransObjects("hkcloud_trans_type");
		
		model.addAttribute("menuHtml", menuHtml);// 左侧菜单html
		Language lang = Lang.getInfo("zh_cn");
		model.addAttribute("lang", lang);
		model.addAttribute("datetime",dataTransitionService.getDate());
		model.addAttribute("squareList",squareList);
		model.addAttribute("siteTypeList",siteTypeList);
		model.addAttribute("siteNameList",siteNameList);
		model.addAttribute("transObjects",transObjects);
		return "dataTransConfig";
	}
	
	
	@RequestMapping("/dataStandardConfig")
	public String dataStandardConfig(PageDataVo pageDataVo, ModelMap model) throws Exception {
		Log log = Log.getLogger();
		// 获取登录用户的信息
		UserInfo userInfo = (UserInfo) session.getAttribute("sessionUserName");
		userInfo = loginService.getUserInfo(userInfo);
		String menuHtml = loginService.getMenuHtml(userInfo, "dataTransition");
		
		String tableName = MessageManager.getMsg("importTable");
		String importCloumns = MessageManager.getMsg("importCloumns");
		String upperCloumns = importCloumns.toUpperCase();
		String[] columns = upperCloumns.split(",");
		Set<String> columnSet = new HashSet<String>();
		for(int i=0;i<columns.length;i++){
			columnSet.add(columns[i]);
		}
		String typeId = "1";
		List<TableColumnInfo> columnList = dataTransitionService.getColumnNames(tableName);
		log.logger.info("+++++++++++++++++++++++++");
		log.logger.info("+++ origin columnList size :"+columnList.size());
		for(int i=0;i<columnList.size();i++){
			log.logger.info("+++ columnName: "+columnList.get(i).getColumnName()+"  columnOrder: "+i);
		}
		log.logger.info("+++++++++++++++++++++++++");
		Iterator<TableColumnInfo> it = columnList.iterator();
		while(it.hasNext()){
			TableColumnInfo tempInfo = it.next();
			if(!columnSet.contains(tempInfo.getColumnName().toUpperCase())){
				it.remove();
			}
		}
		
		log.logger.info("+++++++++++++++++++++++++");
		log.logger.info("+++ after filter columnList size :"+columnList.size());
		for(int i=0;i<columnList.size();i++){
			log.logger.info("+++ columnName: "+columnList.get(i).getColumnName()+"  columnOrder: "+i);
		}
		log.logger.info("+++++++++++++++++++++++++");
		
		Set<String> repeatSet = new HashSet<String>();
		List<TableColumnInfo> newColumnList = new ArrayList<TableColumnInfo>();
		for(int i=0;i<columnList.size();i++){
			TableColumnInfo temp = columnList.get(i);
			if(!repeatSet.contains(temp.getColumnName())){
				newColumnList.add(temp);
				repeatSet.add(temp.getColumnName());
			}
		}
		
		log.logger.info("+++++++++++++++++++++++++");
		log.logger.info("+++ after delete Repeat columnList size :"+newColumnList.size());
		for(int i=0;i<newColumnList.size();i++){
			log.logger.info("+++ columnName: "+newColumnList.get(i).getColumnName()+"  columnOrder: "+i);
		}
		log.logger.info("+++++++++++++++++++++++++");
		
		PosApplication info = new PosApplication();
		info.setTypeId("1");
		info.setUserId(userInfo.getUserId());
		List<PosApplication> templateColumnList = dataTransitionService.getTemplateColumns(info);
		String firstLineAbled = "";
		if(!templateColumnList.isEmpty()){
			PosApplication temp = templateColumnList.get(0);
			firstLineAbled = temp.getFirstLineAbled();
		}
		
		
		Set<String> columnNameSet = new HashSet<String>();
		for(int i=0,len=templateColumnList.size();i<len;i++){
			columnNameSet.add(templateColumnList.get(i).getColumnName());
		}
		
		Iterator<TableColumnInfo> ite = newColumnList.iterator();
		while(ite.hasNext()){
			if(columnNameSet.contains(ite.next().getColumnName())){
				ite.remove();
			}
		}
		
		model.addAttribute("columnList",newColumnList);
		model.addAttribute("templateColumnList",templateColumnList);
		model.addAttribute("firstLineAbled",firstLineAbled);
		model.addAttribute("menuHtml", menuHtml);// 左侧菜单html
		Language lang = Lang.getInfo("zh_cn");
		model.addAttribute("lang", lang);
		model.addAttribute("datetime",dataTransitionService.getDate());
		return "dataStandardConfig";
	}
	
	
	@ResponseBody
	@RequestMapping("/ajaxMappingDataList")
	public Map ajaxMappingDataList(String square,String objectType,String state,String type,String currentPage,String innerName) throws Exception {
		Map<String, Object> map = new HashMap<String, Object>();
		DataTransInfo dataTransInfo = new DataTransInfo();
		UserInfo userInfo = (UserInfo) session.getAttribute("sessionUserName");
		
		List<DataTransInfo> siteTypeList = new ArrayList<DataTransInfo>();
		List<DataTransInfo> siteNameList = new ArrayList<DataTransInfo>();
		
		
		dataTransInfo.setObjectType(objectType);
		dataTransInfo.setSquare(square);
		dataTransInfo.setType(type);
		dataTransInfo.setInnername(URLDecoder.decode(innerName, "UTF-8"));
		dataTransInfo.setState(state);
		
		if(!"-1".equals(square) && "300".equals(objectType)){
			dataTransInfo.setInnerid(square);
			dataTransInfo.setSquare(square);
			dataTransInfo.setParentTypeId(square);
			dataTransInfo.setParentTypeId(objectType);
			DataTransInfo siteType = new DataTransInfo();
			DataTransInfo siteName = new DataTransInfo();
			siteType.setInnerid(objectType);
			siteType.setInnername("广场");
			siteName.setInnerid(square);
			siteName.setInnername("name");
			siteTypeList.add(siteType);
			siteNameList.add(siteName);
		}
		
		// 获取用户信息
		userInfo = loginService.getUserInfo(userInfo);
		// 获取当前系统语言
		String sysLang = session.getAttribute("sysLang").toString();
		// 获取用户语言包
		Language language = Lang.getInfo(sysLang);
		PageDataVo pageDataVo = new PageDataVo();
		// 默认显示第一页
		if (currentPage == null || "".equals(currentPage)) {
			pageDataVo.setCurrentPage(1);
		} else {
			pageDataVo.setCurrentPage(Integer.parseInt(currentPage));
		}
		// 获取每页显示条数
		pageDataVo.setPageSize(Integer.parseInt(MessageManager
				.getMsg("pageSize")));
		// 设置开始显示条数
		pageDataVo.setStartRow((pageDataVo.getCurrentPage() - 1)
				* pageDataVo.getPageSize());
		// 设置参数
		pageDataVo.setSearchParameter(dataTransInfo);
		// 获取当前页用户信息
		pageDataVo = dataTransitionService.getMappingDataList(pageDataVo);
		map.put("language", language);// 系统语言包
		map.put("mappingDataList", pageDataVo.getPageResult());// 结果集
		map.put("siteTypeList", siteTypeList);
		map.put("siteNameList", siteNameList);
		map.put("currentPage", pageDataVo.getCurrentPage());// 当前页
		map.put("maxPage", pageDataVo.getMaxPage());// 最大页
		map.put("recentUserName", userInfo.getUserName());// 当前登录用户名
 		return map;
	}
	
	@ResponseBody
	@RequestMapping("/updateMappingData")
	public Map updateMappingData(String squareId,String objectType,String state,String type,String currentPage,String innerName,String parentNameId,String parentType) throws Exception {
		Map<String, Object> map = new HashMap<String, Object>();
		UserInfo userInfo = (UserInfo) session.getAttribute("sessionUserName");
		
		DataTransInfo dataTransInfo = new DataTransInfo();
		dataTransInfo.setObjectType(objectType);
		dataTransInfo.setType(type);
		dataTransInfo.setInnername(URLDecoder.decode(innerName, "UTF-8"));
		dataTransInfo.setState(state);
		dataTransInfo.setInnername(innerName);
		dataTransInfo.setParentNameId(parentNameId);
		dataTransInfo.setParentType(parentType);
		dataTransInfo.setSquare(squareId);
		dataTransInfo.setInnerid(squareId);
		
		// 获取用户信息
		userInfo = loginService.getUserInfo(userInfo);
		// 获取当前系统语言
		String sysLang = session.getAttribute("sysLang").toString();
		// 获取用户语言包
		Language language = Lang.getInfo(sysLang);
		PageDataVo pageDataVo = new PageDataVo();
		// 默认显示第一页
		if (currentPage == null || "".equals(currentPage)) {
			pageDataVo.setCurrentPage(1);
		} else {
			pageDataVo.setCurrentPage(Integer.parseInt(currentPage));
		}
		// 获取每页显示条数
		pageDataVo.setPageSize(Integer.parseInt(MessageManager
				.getMsg("pageSize")));
		// 设置开始显示条数
		pageDataVo.setStartRow((pageDataVo.getCurrentPage() - 1)
				* pageDataVo.getPageSize());
		// 设置参数
		pageDataVo.setSearchParameter(dataTransInfo);
		// 获取当前页用户信息
		pageDataVo = dataTransitionService.getMappingDataListDetail(pageDataVo);
		map.put("language", language);// 系统语言包
		map.put("mappingDataList", pageDataVo.getPageResult());// 结果集
		map.put("currentPage", pageDataVo.getCurrentPage());// 当前页
		map.put("maxPage", pageDataVo.getMaxPage());// 最大页
		map.put("recentUserName", userInfo.getUserName());// 当前登录用户名
 		return map;
	}
	
	
	@ResponseBody
	@RequestMapping("/updateNameCombo")
	public Map updateNameCombo(String squareId,String objectType,String type,String parentType,String parentName) throws Exception {
		Map<String, Object> map = new HashMap<String, Object>();
		DataTransInfo dataTransInfo = new DataTransInfo();
		
		List<DataTransInfo> siteNameList = new ArrayList<DataTransInfo>();
		String squareName = dataTransitionService.getSiteNameById(squareId);
		
		dataTransInfo.setObjectType(objectType);
		dataTransInfo.setType(type);
		dataTransInfo.setParentName(parentName);
		dataTransInfo.setParentType(parentType);
		dataTransInfo.setSquare(squareId);
		
		if("300".equals(parentType)){
			DataTransInfo squareInfo = new DataTransInfo();
			squareInfo.setInnerid(squareId);
			squareInfo.setInnername(squareName);
			siteNameList.add(squareInfo);
		}else{
			siteNameList = dataTransitionService.getNameListBySiteType(dataTransInfo);
		}
		
		map.put("siteNameList", siteNameList);
 		return map;
	}
	
	@ResponseBody
	@RequestMapping("/ajaxUpdateCombo")
	public Map ajaxUpdateCombo(String squareId,String objectType) throws Exception{
		String squareName = dataTransitionService.getSiteNameById(squareId);
		Map<String,Object> map = new HashMap<String,Object>();
		List<DataTransInfo> siteTypeList = new ArrayList<DataTransInfo>();
		List<DataTransInfo> siteNameList = new ArrayList<DataTransInfo>();
		DataTransInfo nameInfo = new DataTransInfo();
		nameInfo.setInnername(squareName);
		nameInfo.setInnerid(squareId);
		if(objectType==null || "300".equals(objectType)){
			DataTransInfo typeInfo = new DataTransInfo();
			typeInfo.setInnerid(objectType);
			typeInfo.setInnername("广场");
			siteTypeList.add(typeInfo);
			siteNameList.add(nameInfo);
		}else if("400".equals(objectType)){
			if(!dataTransitionService.getQyInGc(squareId).isEmpty()){
				DataTransInfo squareInfo = new DataTransInfo();
				squareInfo.setInnerid("300");
				squareInfo.setInnername("广场");
				siteTypeList.add(squareInfo);
			}if(!dataTransitionService.getQyInQy(squareId).isEmpty()){
				DataTransInfo squareInfo = new DataTransInfo();
				squareInfo.setInnerid("400");
				squareInfo.setInnername("区域");
				siteTypeList.add(squareInfo);
			}if(!dataTransitionService.getQyInLc(squareId).isEmpty()){
				DataTransInfo squareInfo = new DataTransInfo();
				squareInfo.setInnerid("500");
				squareInfo.setInnername("楼层");
				siteTypeList.add(squareInfo);
			}if(!dataTransitionService.getQyInDp(squareId).isEmpty()){
				DataTransInfo squareInfo = new DataTransInfo();
				squareInfo.setInnerid("600");
				squareInfo.setInnername("店铺");
				siteTypeList.add(squareInfo);
			}
		}else if("500".equals(objectType)){
			if(!dataTransitionService.getLcInGc(squareId).isEmpty()){
				DataTransInfo squareInfo = new DataTransInfo();
				squareInfo.setInnerid("300");
				squareInfo.setInnername("广场");
				siteTypeList.add(squareInfo);
			}if(!dataTransitionService.getLcInQy(squareId).isEmpty()){
				DataTransInfo squareInfo = new DataTransInfo();
				squareInfo.setInnerid("400");
				squareInfo.setInnername("区域");
				siteTypeList.add(squareInfo);
			}if(!dataTransitionService.getLcInDp(squareId).isEmpty()){
				DataTransInfo squareInfo = new DataTransInfo();
				squareInfo.setInnerid("600");
				squareInfo.setInnername("店铺");
				siteTypeList.add(squareInfo);
			}
		}else if("600".equals(objectType)){
			if(!dataTransitionService.getDpInGc(squareId).isEmpty()){
				DataTransInfo squareInfo = new DataTransInfo();
				squareInfo.setInnerid("300");
				squareInfo.setInnername("广场");
				siteTypeList.add(squareInfo);
			}if(!dataTransitionService.getDpInQy(squareId).isEmpty()){
				DataTransInfo squareInfo = new DataTransInfo();
				squareInfo.setInnerid("400");
				squareInfo.setInnername("区域");
				siteTypeList.add(squareInfo);
			}if(!dataTransitionService.getDpInLc(squareId).isEmpty()){
				DataTransInfo squareInfo = new DataTransInfo();
				squareInfo.setInnerid("500");
				squareInfo.setInnername("楼层");
				siteTypeList.add(squareInfo);
			}
		}else if("700".equals(objectType)){
			if(!dataTransitionService.getTdInGc(squareId).isEmpty()){
				DataTransInfo squareInfo = new DataTransInfo();
				squareInfo.setInnerid("300");
				squareInfo.setInnername("广场");
				siteTypeList.add(squareInfo);
			}if(!dataTransitionService.getTdInQy(squareId).isEmpty()){
				DataTransInfo squareInfo = new DataTransInfo();
				squareInfo.setInnerid("400");
				squareInfo.setInnername("区域");
				siteTypeList.add(squareInfo);
			}if(!dataTransitionService.getTdInLc(squareId).isEmpty()){
				DataTransInfo squareInfo = new DataTransInfo();
				squareInfo.setInnerid("500");
				squareInfo.setInnername("楼层");
				siteTypeList.add(squareInfo);
			}if(!dataTransitionService.getTdInDp(squareId).isEmpty()){
				DataTransInfo squareInfo = new DataTransInfo();
				squareInfo.setInnerid("600");
				squareInfo.setInnername("店铺");
				siteTypeList.add(squareInfo);
			}
		}
		map.put("siteTypeList", siteTypeList);
		if(siteNameList.isEmpty() && !siteTypeList.isEmpty()){
			siteNameList.add(nameInfo);
		}
		map.put("siteNameList", siteNameList);
		return map;
	}
	
	@ResponseBody
	@RequestMapping("/saveOrUpdate")
	public Map saveOrUpdate(String type,String square,String innerId,String innerName,String outId,String outName,String operation,String state,String objectType,String id) throws Exception {
		Map<String,Object> map = new HashMap<String,Object>();
		DataTransInfo info = new DataTransInfo();
		info.setInnerid(innerId);
		info.setType(type);
		info.setInnername(innerName);
		info.setOutid(outId);
		info.setOutname(outName);
		info.setObjectType(objectType);
		info.setState(state);
		info.setSquare(square);
		info.setId(id == null ? 0 : Long.parseLong(id));
		if(operation == null || "insert".equals(operation)){
			map = dataTransitionService.saveMappingData(info);
		}else{
			dataTransitionService.updateMappingData(info);
		}
		
		return map;
	}
	
	
	public Map saveInfo(DataTransInfo info) throws Exception {
		Map<String,Object> map = new HashMap<String,Object>();
		map = dataTransitionService.saveMappingData(info);
		return map;
	}
	
	@ResponseBody
	@RequestMapping("/deleteMappingData")
	public Map deleteMappingData(String id) throws Exception {
		Map<String,Object> map = new HashMap<String,Object>();
		dataTransitionService.deleteMappingData(id);
		return map;
	}
	
	
	@ResponseBody
	@RequestMapping("/addAllMappingData")
	public Map addAllMappingData(String square,String type) throws Exception {
		List<DataTransInfo> allList = new ArrayList<DataTransInfo>();
		List<DataTransInfo> tempList = new ArrayList<DataTransInfo>();
		DataTransInfo squareInfo = new DataTransInfo();
		String squareName = dataTransitionService.getSiteNameById(square);
		squareInfo.setInnerid(square);
		squareInfo.setInnername(squareName);
		squareInfo.setType("1");
		squareInfo.setOutid(square);
		squareInfo.setOutname(squareName);
		squareInfo.setSquare(square);
		squareInfo.setObjectType("300");
		tempList.add(squareInfo);
		allList.add(squareInfo);
		
		
		DataTransInfo info = new DataTransInfo();
		info.setSquare(square);
		info.setType(type);
		Map<String,Object> map = new HashMap<String,Object>();
		tempList = dataTransitionService.getSiteInGc(info);
		allList.addAll(tempList==null ? new ArrayList<DataTransInfo>() : tempList);
		tempList = dataTransitionService.getSiteInQy(info);
		allList.addAll(tempList==null ? new ArrayList<DataTransInfo>() : tempList);
		tempList = dataTransitionService.getSiteInLc(info);
		allList.addAll(tempList==null ? new ArrayList<DataTransInfo>() : tempList);
		tempList = dataTransitionService.getSiteInDp(info);
		allList.addAll(tempList==null ? new ArrayList<DataTransInfo>() : tempList);
		
		
		List<DataTransInfo> filterList = new ArrayList<DataTransInfo>();
		Set<String> innerIdSet = new HashSet<String>();
		for(int i=0,len=allList.size();i<len;i++){
			DataTransInfo temp = allList.get(i);
			String tempId = temp.getInnerid();
			if(!innerIdSet.contains(tempId)){
				filterList.add(temp);
				innerIdSet.add(tempId);
			}
		}
		
		List<DataTransInfo> mappingList = dataTransitionService.getMappingData(info);
		Set<String> mappingSet = new HashSet<String>();
		for(int i=0,len=mappingList.size();i<len;i++){
			mappingSet.add(mappingList.get(i).getInnerid());
		}
		
		Iterator<DataTransInfo> ite = filterList.iterator();
		while(ite.hasNext()){
			if(mappingSet.contains(ite.next().getInnerid())){
				ite.remove();
			}
		}
		
		for(int i=0,len=filterList.size();i<len;i++){
			saveInfo(filterList.get(i));
		}
		return map;
	}
	
	
	@ResponseBody
	@RequestMapping("/makeAllFiltered")
	public Map makeAllFiltered(String square,String type) throws Exception {
		DataTransInfo info = new DataTransInfo();
		info.setSquare(square);
		info.setType(type);
		Map<String,Object> map = new HashMap<String,Object>();
		dataTransitionService.makeAllFiltered(info);
		
		return map;
	}
	
	@ResponseBody
	@RequestMapping("/makeAPage")
	public Map makeAPage(String idsStr,String state) throws Exception {
		String[] idsArr = idsStr.split(",");
		for(int i=0;i<idsArr.length;i++){
			DataTransInfo info = new DataTransInfo();
			info.setId(Long.parseLong(idsArr[i]));
			info.setState(state);
			Map<String,Object> map = new HashMap<String,Object>();
			dataTransitionService.makeById(info);
		}
		return new HashMap();
	}
	
	private List<SiteAllInfo>  getOriPermissionList(UserInfo userInfo) throws Exception{
		// lowPermission < 300 的是广场以上的权限
		return dataTransitionService.getSitesByUser(userInfo);
	}
	
	private List<SiteAllInfo> getAllGcList(UserInfo userInfo) throws Exception{
		List<SiteAllInfo> list;
		List<SiteAllInfo> oriList = getOriPermissionList(userInfo);
		list = getHighPermission(oriList) <= 300 ? dataTransitionService.getAllGcList(oriList) : new ArrayList<SiteAllInfo>();
		return list;
	}
	
	private int getHighPermission(List<SiteAllInfo> oriList) throws Exception{
		return dataTransitionService.getLowPermission(oriList);
	}
	
	@RequestMapping("/downloadTemplate")
	@ResponseBody
	public Map downloadTemplate(HttpServletRequest request,HttpServletResponse response) throws Exception {
		String fileName = request.getParameter("fileName").toString();
		fileName = java.net.URLDecoder.decode(fileName,"UTF-8");
		
		String dataStr = request.getParameter("dataStr").toString();
		dataStr = java.net.URLDecoder.decode(dataStr,"UTF-8");
		
		String character = request.getParameter("character").toString();
		character = java.net.URLDecoder.decode(character,"UTF-8");
		
		JSONArray ja = JSONArray.fromObject(dataStr);
		ExcelVo excelVo = new ExcelVo();
		excelVo.setFileName(fileName);
		
		SheetVo sheetVo = new SheetVo();
		sheetVo.setSheetNo(1);
		sheetVo.setName("PosImport");
		excelVo.addSheet(sheetVo);
		
		Calendar cal = Calendar.getInstance();
		cal.setTime(new Date());
		
		CellVo cellVo = null;
		for(int j=0;j<10;j++){
			String siteKey = "P00001S00074";
			if(j==0){
				for(int i=0,len=ja.size();i<len;i++){
					cellVo = new CellVo();
					JSONObject jo = ja.getJSONObject(i);
					String columnName = jo.getString("columnName");
					cellVo.setText(columnName);
					cellVo.setColNo(i);
					cellVo.setRowNo(j);
					cellVo.setFormat(new WritableCellFormat());
					sheetVo.addCell(cellVo);
				}
			}else{
				for(int i=0,len=ja.size();i<len;i++){
					cellVo = new CellVo();
					JSONObject jo = ja.getJSONObject(i);
					String columnName = jo.getString("columnName");
					if("SITEKEY".equals(columnName.toUpperCase())){
						cellVo.setText(siteKey);
					}else if("TRANSACTIONTIME".equals(columnName.toUpperCase())){
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
						cellVo.setText(sdf.format(cal.getTime()));
					}else if("TRANSACTIONNUMBER".equals(columnName.toUpperCase())){
						Random rad = new Random();
						cellVo.setText(String.valueOf(rad.nextInt(10)+1));
					}else if("SALESAMOUNT".equals(columnName.toUpperCase())){
						Random rad = new Random();
						cellVo.setText(String.valueOf(Math.floor(rad.nextDouble()*10000)/100));
					}else if("DETAILS".equals(columnName.toUpperCase())){
						cellVo.setText("details");
					}
					cellVo.setColNo(i);
					cellVo.setRowNo(j);
					cellVo.setFormat(new WritableCellFormat());
					sheetVo.addCell(cellVo);
				}
			}
			cal.add(Calendar.SECOND, 5);
		}
		
		SheetVo sheetVoCn = new SheetVo();
		sheetVoCn.setSheetNo(2);
		sheetVoCn.setName("readme_cn");
		excelVo.addSheet(sheetVoCn);
		
		CellVo zz = new CellVo();
		zz.setText("POS模板文件用法说明");
		zz.setRowNo(0);
		zz.setColNo(0);
		zz.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(zz);
		
		CellVo oz = new CellVo();
		oz.setText("中文说明/Chinese ReadMe");
		oz.setRowNo(1);
		oz.setColNo(0);
		oz.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(oz);
		
		CellVo tz = new CellVo();
		tz.setText("POS数据文件名命名规则：");
		tz.setRowNo(2);
		tz.setColNo(0);
		tz.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(tz);
		
		CellVo thz = new CellVo();
		thz.setText("YYYY-MM-DD.ext 其中");
		thz.setRowNo(3);
		thz.setColNo(0);
		thz.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(thz);
		
		CellVo fz = new CellVo();
		fz.setText(" YYYY：年份 如2012");
		fz.setRowNo(4);
		fz.setColNo(0);
		fz.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(fz);
		
		CellVo fvz = new CellVo();
		fvz.setText("MM：月份(01-12)");
		fvz.setRowNo(5);
		fvz.setColNo(0);
		fvz.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(fvz);
		
		CellVo sz = new CellVo();
		sz.setText("DD：天(01-31)");
		sz.setRowNo(6);
		sz.setColNo(0);
		sz.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(sz);
		
		CellVo svz = new CellVo();
		svz.setText("ext:文件扩展名，支持xls,xlsx");
		svz.setRowNo(7);
		svz.setColNo(0);
		svz.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(svz);
		
		CellVo ez = new CellVo();
		ez.setText("例如：2012-03-29.xls");
		ez.setRowNo(8);
		ez.setColNo(0);
		ez.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(ez);
		
		CellVo tenz = new CellVo();
		tenz.setText("列名说明");
		tenz.setRowNo(10);
		tenz.setColNo(0);
		tenz.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(tenz);
		
		CellVo elz = new CellVo();
		elz.setText("列名");
		elz.setRowNo(11);
		elz.setColNo(0);
		elz.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(elz);
		
		CellVo elo = new CellVo();
		elo.setText("类型");
		elo.setRowNo(11);
		elo.setColNo(1);
		elo.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(elo);
		
		CellVo elt = new CellVo();
		elt.setText("说明");
		elt.setRowNo(11);
		elt.setColNo(2);
		elt.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(elt);
		
		CellVo twlz = new CellVo();
		twlz.setText("SiteKey");
		twlz.setRowNo(12);
		twlz.setColNo(0);
		twlz.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(twlz);
		
		CellVo twlo = new CellVo();
		twlo.setText("文本");
		twlo.setRowNo(12);
		twlo.setColNo(1);
		twlo.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(twlo);
		
		CellVo twlt = new CellVo();
		twlt.setText("场所编码，用来标识该POS数据涉及的客流统计范围；该SiteKey可以使用外部系统编码或客流系统内部编码，由客流系统在场所信息接口中配置映射关系。");
		twlt.setRowNo(12);
		twlt.setColNo(2);
		twlt.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(twlt);
		
		CellVo thirz = new CellVo();
		thirz.setText("TransactionTime");
		thirz.setRowNo(13);
		thirz.setColNo(0);
		thirz.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(thirz);
		
		CellVo thiro = new CellVo();
		thiro.setText("日期");
		thiro.setRowNo(13);
		thiro.setColNo(1);
		thiro.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(thiro);
		
		CellVo thirt = new CellVo();
		thirt.setText("交易时间，该记录信息发生的时间,24小时制，格式：YYYY-M-D HH:MM:SS");
		thirt.setRowNo(13);
		thirt.setColNo(2);
		thirt.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(thirt);
		
		CellVo fourz = new CellVo();
		fourz.setText("TransactionNumber");
		fourz.setRowNo(14);
		fourz.setColNo(0);
		fourz.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(fourz);
		
		CellVo fouro = new CellVo();
		fouro.setText("文本（整数）");
		fouro.setRowNo(14);
		fouro.setColNo(1);
		fouro.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(fouro);
		
		CellVo fourt = new CellVo();
		fourt.setText("交易笔数，该记录信息包含的交易数量");
		fourt.setRowNo(14);
		fourt.setColNo(2);
		fourt.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(fourt);
		
		CellVo fifz = new CellVo();
		fifz.setText("SalesAmount");
		fifz.setRowNo(15);
		fifz.setColNo(0);
		fifz.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(fifz);
		
		CellVo fifo = new CellVo();
		fifo.setText("数值（浮点）");
		fifo.setRowNo(15);
		fifo.setColNo(1);
		fifo.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(fifo);
		
		CellVo fift = new CellVo();
		fift.setText("交易额，该记录信息的实际交易");
		fift.setRowNo(15);
		fift.setColNo(2);
		fift.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(fift);
		
		CellVo sisz = new CellVo();
		sisz.setText("Details");
		sisz.setRowNo(16);
		sisz.setColNo(0);
		sisz.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(sisz);
		
		CellVo siso = new CellVo();
		siso.setText("文本");
		siso.setRowNo(16);
		siso.setColNo(1);
		siso.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(siso);
		
		CellVo sist = new CellVo();
		sist.setText("备注，与该交易信息相关的信息");
		sist.setRowNo(16);
		sist.setColNo(2);
		sist.setFormat(new WritableCellFormat());
		sheetVoCn.addCell(sist);
		
		
		createExcel(excelVo, response, character);
		HashMap<String , Object> paraMap=new HashMap<String, Object>();
		paraMap.put("result", fileName);
		return paraMap;
	}
	
	
	/**
	 * 生成Excel文件
	 * 
	 * @param excelVo
	 *            文件实体类
	 * @param os
	 *            输出流
	 * @throws IOException
	 * @throws WriteException
	 * @throws RowsExceededException
	 */
	private void createExcel(ExcelVo excelVo, HttpServletResponse response,String character)
			throws IOException, RowsExceededException, WriteException {
		// 保证不乱码
		response.setContentType("application/x-msdownload;charset=UTF-8");
		response.setCharacterEncoding("UTF-8");
		if("utf-8".equals(character)){
		response.setHeader("Content-Disposition", "attachment;" + " filename="
				+ URLEncoder.encode(excelVo.getFileName() + ".xls", "utf-8"));
		}else{
			response.setHeader("Content-Disposition", "attachment;" + " filename="+new String(excelVo.getFileName().getBytes("GB2312"),"ISO-8859-1")+ ".xls");
		}
		// 创建输出流
		OutputStream os = response.getOutputStream();
		// 创建工作表	
		WritableWorkbook workBook = Workbook.createWorkbook(os);
		SheetVo sheet = excelVo.getSheet();
		while (sheet != null) {
			WritableSheet workSheet = workBook.createSheet(sheet.getName(),
					sheet.getSheetNo());
			CellVo cell = sheet.getCell();
			while (cell != null) {
				Label workCell = new Label(cell.getColNo(), cell.getRowNo(),
						cell.getText(), cell.getFormat());
				workSheet.addCell(workCell);
				cell = sheet.getCell();
			}
			sheet = excelVo.getSheet();
		}
		workBook.write();
		workBook.close();
		os.close();
	}
	
	
	@RequestMapping("/saveTemplate")
	@ResponseBody
	public Map saveTemplate(String dataStr) throws Exception {
		
		
		// 获取登录用户的信息
		UserInfo userInfo = (UserInfo) session.getAttribute("sessionUserName");
		userInfo = loginService.getUserInfo(userInfo);
		dataStr = java.net.URLDecoder.decode(dataStr,"UTF-8");
		JSONArray ja = JSONArray.fromObject(dataStr);
		
		Set<String> newColumnNameSet = new HashSet<String>();
		for(int i=0,len=ja.size();i<len;i++){
			JSONObject jo = ja.getJSONObject(i);
			newColumnNameSet.add(jo.getString("columnName")==null? "":jo.getString("columnName"));
		}
		
		JSONObject temp = ja.getJSONObject(0);
		String typeId = temp.getString("typeId");
		PosApplication searchInfo = new PosApplication();
		searchInfo.setTypeId(typeId);
		searchInfo.setUserId(userInfo.getUserId());
		List<PosApplication> templateColumns = dataTransitionService.getTemplateColumns(searchInfo);
		for(int i=0,len=templateColumns.size();i<len;i++){
			if(!newColumnNameSet.contains(templateColumns.get(i).getColumnName())){
				PosApplication tempInfo = new PosApplication();
				tempInfo.setUserId(userInfo.getUserId());
				tempInfo.setColumnName(templateColumns.get(i).getColumnName());
				tempInfo.setTypeId(templateColumns.get(i).getTypeId());
				tempInfo.setTableName(templateColumns.get(i).getTableName());
				dataTransitionService.deleteTemplateColumn(tempInfo);
			}
		}
		Set<String> columnNameSet = new HashSet<String>();
		for(int i=0,len=templateColumns.size();i<len;i++){
			columnNameSet.add(templateColumns.get(i).getColumnName());
		}
		
		
		for(int i=0,len=ja.size();i<len;i++){
			JSONObject jo = ja.getJSONObject(i);
			PosApplication info = new PosApplication();
			info.setColumnAlias(jo.getString("columnAlias")==null? "":jo.getString("columnAlias"));
			info.setColumnName(jo.getString("columnName")==null? "":jo.getString("columnName"));
			info.setTypeId(jo.getString("typeId")==null? "":jo.getString("typeId"));
			info.setColumnOrder(i);
			info.setDataType(jo.getString("dataType")==null? "":jo.getString("dataType"));
			info.setFirstLineAbled(jo.getString("firstLineAbled")==null? "":jo.getString("firstLineAbled"));
			info.setTableName(jo.getString("tableName")==null? "":jo.getString("tableName"));
			if(columnNameSet.contains(jo.getString("columnName"))){
				info.setModifiedtime(new Date());
				info.setUserId(userInfo.getUserId());
				dataTransitionService.updateTemplateColumn(info);
			}else if(!"".equals(jo.getString("columnName"))){
				info.setCreatetime(new Date());
				info.setModifiedtime(new Date());
				info.setUserId(userInfo.getUserId());
				dataTransitionService.saveTemplateColumn(info);
			}
		}
		return new HashMap();
	}
}
